import sqlite3, os

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

con=sqlite3.connect(os.path.join(BASE_DIR, 'database.db'))

def try_create_table():

    command1 = '''
        CREATE TABLE IF NOT EXISTS users
        (
            user_id VARCHAR(50) NOT NULL,
            nickname VARCHAR(30) NOT NULL,
            password VARCHAR(40) NOT NULL,
            balance_day INT NOT NULL DEFAULT 0,
            create_at INT NOT NULL,
            PRIMARY KEY (user_id)
        )
        '''
    command2 = '''
        CREATE TABLE IF NOT EXISTS orders
        (
            order_id INT PRIMARY KEY,
            user_id VARCHAR(50) NOT NULL,
            add_day INT DEFAULT 0,
            give_day INT DEFAULT 0,
            friend_id VARCHAR(50) DEFAULT "",
            create_at INT NOT NULL,
            FOREIGN KEY (user_id) REFERENCES user(user_id)
        )
        '''
    cur=con.cursor()
    cur.execute(command1)
    cur.execute(command2)
    cur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
    values = cur.fetchall()
    print(values)
    
    cur.execute("SELECT * FROM users")
    values = cur.fetchall()
    print(values)

    cur.execute("SELECT * FROM orders")
    values = cur.fetchall()
    print(values)

    cur.close()
    con.close()


try_create_table()